# @Time : 2021/3/6 9:40
# @Author : Fioman
# @Phone : 13149920693
import random

from db_mysql.mysql_operator import MysqlOperator
from db_tools.db_handler import DbHandler
import time
import datetime


def get_six_random_number():
    s = ""
    for i in range(6):
        ch = chr(random.randrange(ord('0'), ord('9') + 1))
        s += ch
    return s


def get_group_number_int():
    nowSecond = time.time()
    lastSix = get_six_random_number()
    now = time.strftime("%Y%m%d%H%M%S", time.localtime(nowSecond))[:12]
    groupNumber = now + lastSix
    return int(groupNumber)


def get_create_time():
    date = datetime.datetime.now().strftime("%Y-%m-%d %H-%M-%S")
    return str(date)


class SqlHandler(object):
    def __init__(self):
        # 先去获取表名,如果找不到这个表名,就创建
        self.db = DbHandler()  # mogodb数据库
        # 获取eap通信的一些参数.主要是数据表的轮询时间,还有就是自动和手动模式
        self.reOpenSchedlerFlag = False  # 是否要重新开启定时任务.
        # 首先是创建8张表,表格不要使用自增主键的方式,因为自增主键在数据插入的时候老是出现键冲突错误问题.
        self.create_table_eqp_data_status()
        self.create_table_qp_data_alarm()

    def covert_datetime_to_string(self, resList):
        newList = []
        for data in resList:
            if "CREATETIME" in data.keys():
                dateTime = data.get("CREATETIME")
                newDateTime = dateTime.strftime("%Y-%m-%d %H:%M:%S")
                data["CREATETIME"] = newDateTime
            newList.append(data)
        return newList

    def create_table_eqp_data_status(self):
        """
        创建不实时采集设备状态表.
        :return:
        """
        with MysqlOperator(logTime=True) as sp:
            sql = "Create Table If Not Exists eqp_data_status" + "(" + \
                  "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
                  "STATUSCODE VARCHAR(30)," + \
                  "STATUSDES VARCHAR(200)," + \
                  "STATUSVALUE VARCHAR(50)," + \
                  "CREATETIME DATETIME" + \
                  ")"
            sp.create_table(sql)

    # 删除旧数据,更新新数据.
    def update_table_eqp_data_status(self):
        """
        获取当前设备状态，并放入数据库中
        :return:
        """
        try:
            # 先去获取数据,如果有数据就更新,如果没有,再插入.
            insertData = self.get_eqp_data_status_value()
            with MysqlOperator(logTime=True) as sp:
                tableName = "eqp_data_status"
                # 1. 先删除旧表中的数据
                sp.delete_table(tableName)
                for data in insertData:
                    sql = "insert into {}(STATUSCODE,STATUSDES,STATUSVALUE,CREATETIME)".format(tableName) + \
                          " values(%s,%s,%s,%s)"
                    sp.insert_data(sql, data)
        except Exception as e:
            print("in update_table_pc_data_status() error : {},重新再更新一次".format(str(e)))
            # 先去获取数据,如果有数据就更新,如果没有,再插入.
            insertData = self.get_eqp_data_status_value()
            with MysqlOperator(logTime=True) as sp:
                tableName = "eqp_data_status"
                # 1. 先删除旧表中的数据
                sp.delete_table(tableName)
                for data in insertData:
                    sql = "insert into {}(ID,STATUSCODE,STATUSDES,STATUSVALUE,CREATETIME)".format(tableName) + \
                          " values(%s,%s,%s,%s,%s)"
                    sp.insert_data(sql, data)

    def get_eqp_data_status_value(self):
        """
        获取工艺质量表里面的数据.
        :return:
        """
        data = self.db.get_working_info()[1]
        mainZmcRunning = data.get("mainZmcRunning", "0")

        # 再写入一次,防止一些数据没有初始化的情况
        newValues = {
            "$set": {
                "mainZmcRunning": mainZmcRunning,
            }
        }

        self.db.save_working_info_data(newValues)

        createTime = get_create_time()
        dataValue = [
            ("MAINZMCRUNNING", "收板机控制器状态 运行/停止/急停", mainZmcRunning, createTime),
        ]

        return dataValue

    def create_table_qp_data_alarm(self):
        """
        创建不实时采集设备异常状态表.
        :return:
        """
        with MysqlOperator(logTime=True) as sp:
            sql = "Create Table If Not Exists qp_data_alarm" + "(" + \
                  "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
                  "ALARMCODE VARCHAR(30)," + \
                  "ALARMDES VARCHAR(200)," + \
                  "ALARMVALUE VARCHAR(50)," + \
                  "CREATETIME DATETIME" + \
                  ")"
            sp.create_table(sql)

    # 删除旧数据,更新新数据.
    def update_table_qp_data_alarm(self, ALARMDES,ALARMVALUE):
        """
        工艺质量表的数据插入.插入的数据是从knife中获取的.
        :return:
        """
        try:
            # 这里是用来上传设备报警异常的表，只添加，删除由上位机进行
            insertData = self.get_qp_data_alarm_value(ALARMDES,ALARMVALUE)
            with MysqlOperator(logTime=True) as sp:
                tableName = "qp_data_alarm"
                # 1. 先删除旧表中的数据
                for data in insertData:
                    sql = "insert into {}(ALARMCODE,ALARMDES,ALARMVALUE,CREATETIME)".format(tableName) + \
                          " values(%s,%s,%s,%s)"
                    sp.insert_data(sql, data)
        except Exception as e:
            print("in update_table_pc_data_status() error : {},重新再更新一次".format(str(e)))
            # 先去获取数据,如果有数据就更新,如果没有,再插入.
            insertData = self.get_qp_data_alarm_value(ALARMDES,ALARMVALUE)
            with MysqlOperator(logTime=True) as sp:
                tableName = "qp_data_alarm"
                for data in insertData:
                    sql = "insert into {}(ID,ALARMCODE,ALARMDES,ALARMVALUE,CREATETIME)".format(tableName) + \
                          " values(%s,%s,%s,%s,%s)"
                    sp.insert_data(sql, data)

    def get_qp_data_alarm_value(self, ALARMDES, ALARMVALUE):
        """
        异常状态存储表
        :return:
        """
        createTime = get_create_time()
        dataValue = [
            ("HOLE_ALARM_1", ALARMDES, ALARMVALUE, createTime),
        ]

        return dataValue


if __name__ == '__main__':
    sqlHr = SqlHandler()
    # 数据库重建
    a = sqlHr.update_table_qp_data_alarm("控制器通信异常", 1)
    print(a)
    tableName = "EAP_SIGNAL_STATUS"

